{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA_PRE_DIR = './datasets/radar'\n",
    "DATA_AFTER_DIR = './datasets/cache'\n",
    "\n",
    "FEATURES_COLUMNS = ['航班号(I170)', '经纬度(I130)', '系统接收时间', '地速(I160)', '几何高度(I140)', '飞行高度(I145)', '航向(I160)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_longitude(value: str, **extra):\n",
    "    if value is None or value is pd.NA or value.strip() == '':\n",
    "        return pd.NA\n",
    "    return float(value.split(',')[0])\n",
    "\n",
    "\n",
    "def get_latitude(value: str, **extra):\n",
    "    if value is None or value is pd.NA or value.strip() == '':\n",
    "        return pd.NA\n",
    "    return float(value.split(',')[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "p = os.path.join(DATA_PRE_DIR, '20210401.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>航班号</th>\n",
       "      <th>经度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1564338</th>\n",
       "      <td>AKJC1234</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1564339</th>\n",
       "      <td>QTR895</td>\n",
       "      <td>97.268</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              航班号      经度\n",
       "1564338  AKJC1234    <NA>\n",
       "1564339  QTR895    97.268"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df: pd.DataFrame = pd.read_table(p, sep='\\t', encoding='UTF-8')\n",
    "# 空串替换为NA\n",
    "df.replace(to_replace=r'^\\s*$', value=pd.NA, regex=True, inplace=True)\n",
    "# 0.00替换为NA\n",
    "df.replace(to_replace=r'^\\s*?0*?\\.?0*?\\s*?$', value=pd.NA, regex=True, inplace=True)\n",
    "# 无航班号和时间的删除\n",
    "df.dropna(axis=0, how='any', subset=['航班号(I170)', '系统接收时间'], inplace=True)\n",
    "df = df.loc[:, FEATURES_COLUMNS].copy(deep=True)\n",
    "# 经纬度提取\n",
    "df['经度'] = df.loc[:, '经纬度(I130)'].apply(get_longitude)\n",
    "df['纬度'] = df.loc[:, '经纬度(I130)'].apply(get_latitude)\n",
    "df.drop(['经纬度(I130)'], axis=1, inplace=True)\n",
    "# 插值\n",
    "# TODO\n",
    "# 类型\n",
    "# df[['航班号(I170)', '系统接收时间']] = df[['航班号(I170)', '系统接收时间']].astype(str)\n",
    "# df[['经度', '纬度', '地速(I160)', '几何高度(I140)', '飞行高度(I145)', '航向(I160)']] = df[['经度', '纬度', '地速(I160)', '几何高度(I140)', '飞行高度(I145)', '航向(I160)']].astype(float)\n",
    "\n",
    "pnames = set(df['航班号(I170)'].to_list())\n",
    "df.drop(['飞行高度(I145)'], axis=1, inplace=True)\n",
    "df.rename(columns={\n",
    "    '航班号(I170)': '航班号',\n",
    "    '地速(I160)': '速度',\n",
    "    '几何高度(I140)': '高度',\n",
    "    '航向(I160)': '航向'\n",
    "}, inplace=True)\n",
    "\n",
    "df.loc[1564338:1564339, ('航班号', '经度')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>航班号</th>\n",
       "      <th>高度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>OKA3044</td>\n",
       "      <td>10858.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>HXA4942</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CES2770</td>\n",
       "      <td>1249.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>HXA4884</td>\n",
       "      <td>541.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>HXA4884</td>\n",
       "      <td>541.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HXA4884</td>\n",
       "      <td>541.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CLX7441</td>\n",
       "      <td>10660.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>CLX7441</td>\n",
       "      <td>10660.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>CES2278</td>\n",
       "      <td>10332.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>CES2376</td>\n",
       "      <td>7056.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>CSC8814</td>\n",
       "      <td>9448.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>CSC8814</td>\n",
       "      <td>9448.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         航班号        高度\n",
       "19  OKA3044   10858.50\n",
       "20  HXA4942       <NA>\n",
       "21  CES2770    1249.68\n",
       "22  HXA4884     541.02\n",
       "23  HXA4884     541.02\n",
       "24  HXA4884     541.02\n",
       "25  CLX7441   10660.38\n",
       "26  CLX7441   10660.38\n",
       "27  CES2278   10332.72\n",
       "28  CES2376    7056.12\n",
       "29  CSC8814    9448.80\n",
       "30  CSC8814    9448.80"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[19:30, ('航班号', '高度')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['10858.50', 'OKA3044 ', '10858.50'],\n",
       " [<NA>, 'HXA4942 ', <NA>],\n",
       " ['1249.68', 'CES2770 ', '1249.68'],\n",
       " ['541.02', 'HXA4884 ', '541.02'],\n",
       " ['541.02', 'HXA4884 ', '541.02'],\n",
       " ['541.02', 'HXA4884 ', '541.02'],\n",
       " ['10660.38', 'CLX7441 ', '10660.38'],\n",
       " ['10660.38', 'CLX7441 ', '10660.38'],\n",
       " ['10332.72', 'CES2278 ', '10332.72'],\n",
       " ['7056.12', 'CES2376 ', '7056.12'],\n",
       " ['9448.80', 'CSC8814 ', '9448.80'],\n",
       " ['9448.80', 'CSC8814 ', '9448.80']]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[19:30, ('高度', '航班号', '高度')].to_numpy().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>航班号</th>\n",
       "      <th>高度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>OKA3044</td>\n",
       "      <td>10858.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>HXA4942</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CES2770</td>\n",
       "      <td>1249.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>HXA4884</td>\n",
       "      <td>541.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>HXA4884</td>\n",
       "      <td>541.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HXA4884</td>\n",
       "      <td>541.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CLX7441</td>\n",
       "      <td>10660.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>CLX7441</td>\n",
       "      <td>10660.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>CES2278</td>\n",
       "      <td>10332.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>CES2376</td>\n",
       "      <td>7056.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>CSC8814</td>\n",
       "      <td>9448.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>CSC8814</td>\n",
       "      <td>9448.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         航班号        高度\n",
       "19  OKA3044   10858.50\n",
       "20  HXA4942       <NA>\n",
       "21  CES2770    1249.68\n",
       "22  HXA4884     541.02\n",
       "23  HXA4884     541.02\n",
       "24  HXA4884     541.02\n",
       "25  CLX7441   10660.38\n",
       "26  CLX7441   10660.38\n",
       "27  CES2278   10332.72\n",
       "28  CES2376    7056.12\n",
       "29  CSC8814    9448.80\n",
       "30  CSC8814    9448.80"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[19:30, ('航班号', '高度')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "      <th>29</th>\n",
       "      <th>30</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>航班号</th>\n",
       "      <td>OKA3044</td>\n",
       "      <td>HXA4942</td>\n",
       "      <td>CES2770</td>\n",
       "      <td>HXA4884</td>\n",
       "      <td>HXA4884</td>\n",
       "      <td>HXA4884</td>\n",
       "      <td>CLX7441</td>\n",
       "      <td>CLX7441</td>\n",
       "      <td>CES2278</td>\n",
       "      <td>CES2376</td>\n",
       "      <td>CSC8814</td>\n",
       "      <td>CSC8814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>高度</th>\n",
       "      <td>10858.50</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>1249.68</td>\n",
       "      <td>541.02</td>\n",
       "      <td>541.02</td>\n",
       "      <td>541.02</td>\n",
       "      <td>10660.38</td>\n",
       "      <td>10660.38</td>\n",
       "      <td>10332.72</td>\n",
       "      <td>7056.12</td>\n",
       "      <td>9448.80</td>\n",
       "      <td>9448.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           19        20        21        22        23        24        25  \\\n",
       "航班号  OKA3044   HXA4942   CES2770   HXA4884   HXA4884   HXA4884   CLX7441    \n",
       "高度   10858.50      <NA>   1249.68    541.02    541.02    541.02  10660.38   \n",
       "\n",
       "           26        27        28        29        30  \n",
       "航班号  CLX7441   CES2278   CES2376   CSC8814   CSC8814   \n",
       "高度   10660.38  10332.72   7056.12   9448.80   9448.80  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[19:30, ('航班号', '高度')].T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['OKA3044 ',\n",
       "  'HXA4942 ',\n",
       "  'CES2770 ',\n",
       "  'HXA4884 ',\n",
       "  'HXA4884 ',\n",
       "  'HXA4884 ',\n",
       "  'CLX7441 ',\n",
       "  'CLX7441 ',\n",
       "  'CES2278 ',\n",
       "  'CES2376 ',\n",
       "  'CSC8814 ',\n",
       "  'CSC8814 '],\n",
       " ['10858.50',\n",
       "  <NA>,\n",
       "  '1249.68',\n",
       "  '541.02',\n",
       "  '541.02',\n",
       "  '541.02',\n",
       "  '10660.38',\n",
       "  '10660.38',\n",
       "  '10332.72',\n",
       "  '7056.12',\n",
       "  '9448.80',\n",
       "  '9448.80']]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[19:30, ('航班号', '高度')].T.to_numpy().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "475ea21d7e2ced9e016f53aa8edd27ecb1fe8de7c44be378c61a9513ecbbbc40"
  },
  "kernelspec": {
   "display_name": "Python 3.6.8 ('.venv': pipenv)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
